


--use PolePosition

-- // checksum failed custom error
--exec sp_addmessage @msgnum = 505050, @severity = 16, @msgtext = N'%s(%s) checksum compare failed', @replace = 'REPLACE', @lang = 'us_english'
GO

-- // not found custom error
--exec sp_addmessage @msgnum = 505060, @severity = 16, @msgtext = N'%s(%s) does not exist', @replace = 'REPLACE', @lang = 'us_english'
GO

-- // unit test custom error
--exec sp_addmessage @msgnum = 505070, @severity = 16, @msgtext = N'%s Test Failed. Expected< %s > Actual< %s > %s', @replace = 'REPLACE', @lang = 'us_english'
GO



--use PolePosition

if exists (select * from dbo.sysobjects where id = object_id(N'v_tb_Vendor_InspVisit_XmissionStructures_Results') and OBJECTPROPERTY(id, N'IsProcedure') = 0)
drop view v_tb_Vendor_InspVisit_XmissionStructures_Results
GO

create view v_tb_Vendor_InspVisit_XmissionStructures_Results as
	/* 	Initial Generated Date:  Saturday, February 07, 2009 
		Purpose: Selected record(s) from the table; this view is the source for all procedures' select statements
	*/
	
select
	 [Vendor_InspVisit_XmissionStructures_ResultId] as [Vendor_InspVisit_XmissionStructures_ResultId]
	,[Vendor_InspVisit_XMissionStructure_PoleFk] as [Vendor_InspVisit_XMissionStructure_PoleFk]
	,[Vendor_InspVisit_XmissionStructures_Inspection_TypeFk] as [Vendor_InspVisit_XmissionStructures_Inspection_TypeFk]
	,[Vendor_InspVisit_XmissionStructures_ResultTypeFk] as [Vendor_InspVisit_XmissionStructures_ResultTypeFk]
	,[LastInspection] as [LastInspection]
	,[Comments] as [Comments]
	,[bElectrical] as [bElectrical]
	-- customization may be required for FriendlyName
	,Left(CONVERT(varchar(255), [Vendor_InspVisit_XmissionStructures_ResultId]), 255) as [FriendlyName]
from
	tb_Vendor_InspVisit_XmissionStructures_Results
GO





--use PolePosition

if exists (select * from dbo.sysobjects where id = object_id(N'up_VendorInspVisitXmissionStructuresResult_Test') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure up_VendorInspVisitXmissionStructuresResult_Test
GO


create proc up_VendorInspVisitXmissionStructuresResult_Test
	@Vendor_InspVisit_XmissionStructures_ResultId int
as
	/* 	Initial Generated Date:  Saturday, February 07, 2009 
		Purpose: Test if a record exists, using the primary key
	*/

SET NOCOUNT ON	
	
if exists(
	select [Vendor_InspVisit_XmissionStructures_ResultId] 
	from [tb_Vendor_InspVisit_XmissionStructures_Results] source
	where [source].[Vendor_InspVisit_XmissionStructures_ResultId] = @Vendor_InspVisit_XmissionStructures_ResultId)
begin
	return(1)
end
else
begin
	return(0)
end
GO






--use PolePosition

if exists (select * from dbo.sysobjects where id = object_id(N'up_VendorInspVisitXmissionStructuresResult_Select') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure up_VendorInspVisitXmissionStructuresResult_Select
GO

create proc up_VendorInspVisitXmissionStructuresResult_Select
	 @Vendor_InspVisit_XmissionStructures_ResultId int = null 
	,@Vendor_InspVisit_XMissionStructure_PoleFk int = null 
	,@Vendor_InspVisit_XmissionStructures_Inspection_TypeFk int = null 
	,@Vendor_InspVisit_XmissionStructures_ResultTypeFk int = null 
	,@LastInspection nvarchar(50) = null 
	,@Comments ntext = null 
	,@bElectrical bit = null 
as
	/* 	Initial Generated Date:  Saturday, February 07, 2009 
		Purpose: Selected record(s) based on the passed arguments; no arguments returns all
	*/

SET NOCOUNT ON	

declare @table table
	(
	 Vendor_InspVisit_XmissionStructures_ResultId int
	,Vendor_InspVisit_XMissionStructure_PoleFk int
	,Vendor_InspVisit_XmissionStructures_Inspection_TypeFk int
	,Vendor_InspVisit_XmissionStructures_ResultTypeFk int
	,LastInspection nvarchar(50)
	,Comments ntext
	,bElectrical bit
	,[FriendlyName] varchar(255)
	)
	
insert @table
	(
	 [Vendor_InspVisit_XmissionStructures_ResultId]
	,[Vendor_InspVisit_XMissionStructure_PoleFk]
	,[Vendor_InspVisit_XmissionStructures_Inspection_TypeFk]
	,[Vendor_InspVisit_XmissionStructures_ResultTypeFk]
	,[LastInspection]
	,[Comments]
	,[bElectrical]
	,[FriendlyName]
	)
select 
	 [Vendor_InspVisit_XmissionStructures_ResultId]
	,[Vendor_InspVisit_XMissionStructure_PoleFk]
	,[Vendor_InspVisit_XmissionStructures_Inspection_TypeFk]
	,[Vendor_InspVisit_XmissionStructures_ResultTypeFk]
	,[LastInspection]
	,[Comments]
	,[bElectrical]
	,[FriendlyName]
from v_tb_Vendor_InspVisit_XmissionStructures_Results
where
		IsNull([v_tb_Vendor_InspVisit_XmissionStructures_Results].[Vendor_InspVisit_XmissionStructures_ResultId], -1) = Coalesce(@Vendor_InspVisit_XmissionStructures_ResultId, [v_tb_Vendor_InspVisit_XmissionStructures_Results].[Vendor_InspVisit_XmissionStructures_ResultId], -1)
	AND	IsNull([v_tb_Vendor_InspVisit_XmissionStructures_Results].[Vendor_InspVisit_XMissionStructure_PoleFk], -1) = Coalesce(@Vendor_InspVisit_XMissionStructure_PoleFk, [v_tb_Vendor_InspVisit_XmissionStructures_Results].[Vendor_InspVisit_XMissionStructure_PoleFk], -1)
	AND	IsNull([v_tb_Vendor_InspVisit_XmissionStructures_Results].[Vendor_InspVisit_XmissionStructures_Inspection_TypeFk], -1) = Coalesce(@Vendor_InspVisit_XmissionStructures_Inspection_TypeFk, [v_tb_Vendor_InspVisit_XmissionStructures_Results].[Vendor_InspVisit_XmissionStructures_Inspection_TypeFk], -1)
	AND	IsNull([v_tb_Vendor_InspVisit_XmissionStructures_Results].[Vendor_InspVisit_XmissionStructures_ResultTypeFk], -1) = Coalesce(@Vendor_InspVisit_XmissionStructures_ResultTypeFk, [v_tb_Vendor_InspVisit_XmissionStructures_Results].[Vendor_InspVisit_XmissionStructures_ResultTypeFk], -1)
	AND	IsNull([v_tb_Vendor_InspVisit_XmissionStructures_Results].[LastInspection], '') = Coalesce(@LastInspection, [v_tb_Vendor_InspVisit_XmissionStructures_Results].[LastInspection], '')
	AND	IsNull(Convert(varchar(2000), [v_tb_Vendor_InspVisit_XmissionStructures_Results].[Comments]), '') = Coalesce(Convert(varchar(2000), @Comments), Convert(varchar(2000), [v_tb_Vendor_InspVisit_XmissionStructures_Results].[Comments]), '')
	AND	IsNull([v_tb_Vendor_InspVisit_XmissionStructures_Results].[BElectrical], 0) = Coalesce(@BElectrical, [v_tb_Vendor_InspVisit_XmissionStructures_Results].[BElectrical], 0)

-- primary select
select * from @table





	-- VendorInspVisitXmissionStructuresImageCollection select

select
	v_tb_Vendor_InspVisit_XmissionStructures_Images.* 
from 
	v_tb_Vendor_InspVisit_XmissionStructures_Images 
	JOIN @table x ON v_tb_Vendor_InspVisit_XmissionStructures_Images.Vendor_InspVisit_XmissionStructures_ResultFk = x.Vendor_InspVisit_XmissionStructures_ResultId
	

	
GO 





--use PolePosition

if exists (select * from dbo.sysobjects where id = object_id(N'up_VendorInspVisitXmissionStructuresResult_Select_Top1') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure up_VendorInspVisitXmissionStructuresResult_Select_Top1
GO

create proc up_VendorInspVisitXmissionStructuresResult_Select_Top1
	 @Vendor_InspVisit_XmissionStructures_ResultId int = null 
	,@Vendor_InspVisit_XMissionStructure_PoleFk int = null 
	,@Vendor_InspVisit_XmissionStructures_Inspection_TypeFk int = null 
	,@Vendor_InspVisit_XmissionStructures_ResultTypeFk int = null 
	,@LastInspection nvarchar(50) = null 
	,@Comments ntext = null 
	,@bElectrical bit = null 
as
	/* 	Initial Generated Date:  Saturday, February 07, 2009 
		Purpose: Selected record(s) based on the passed arguments; no arguments returns all
	*/

SET NOCOUNT ON	

declare @table table
	(
	  Vendor_InspVisit_XmissionStructures_ResultId int
	, Vendor_InspVisit_XMissionStructure_PoleFk int
	, Vendor_InspVisit_XmissionStructures_Inspection_TypeFk int
	, Vendor_InspVisit_XmissionStructures_ResultTypeFk int
	, LastInspection nvarchar(50)
	, Comments ntext
	, bElectrical bit
	,[FriendlyName] varchar(255)
	)

insert @table
	(
	 [Vendor_InspVisit_XmissionStructures_ResultId]
	,[Vendor_InspVisit_XMissionStructure_PoleFk]
	,[Vendor_InspVisit_XmissionStructures_Inspection_TypeFk]
	,[Vendor_InspVisit_XmissionStructures_ResultTypeFk]
	,[LastInspection]
	,[Comments]
	,[bElectrical]
	,[FriendlyName]
	)
select TOP 1
	 [Vendor_InspVisit_XmissionStructures_ResultId]
	,[Vendor_InspVisit_XMissionStructure_PoleFk]
	,[Vendor_InspVisit_XmissionStructures_Inspection_TypeFk]
	,[Vendor_InspVisit_XmissionStructures_ResultTypeFk]
	,[LastInspection]
	,[Comments]
	,[bElectrical]
	,[FriendlyName]
from v_tb_Vendor_InspVisit_XmissionStructures_Results
where
		IsNull([v_tb_Vendor_InspVisit_XmissionStructures_Results].[Vendor_InspVisit_XmissionStructures_ResultId], -1) = Coalesce(@Vendor_InspVisit_XmissionStructures_ResultId, [v_tb_Vendor_InspVisit_XmissionStructures_Results].[Vendor_InspVisit_XmissionStructures_ResultId], -1)
	AND	IsNull([v_tb_Vendor_InspVisit_XmissionStructures_Results].[Vendor_InspVisit_XMissionStructure_PoleFk], -1) = Coalesce(@Vendor_InspVisit_XMissionStructure_PoleFk, [v_tb_Vendor_InspVisit_XmissionStructures_Results].[Vendor_InspVisit_XMissionStructure_PoleFk], -1)
	AND	IsNull([v_tb_Vendor_InspVisit_XmissionStructures_Results].[Vendor_InspVisit_XmissionStructures_Inspection_TypeFk], -1) = Coalesce(@Vendor_InspVisit_XmissionStructures_Inspection_TypeFk, [v_tb_Vendor_InspVisit_XmissionStructures_Results].[Vendor_InspVisit_XmissionStructures_Inspection_TypeFk], -1)
	AND	IsNull([v_tb_Vendor_InspVisit_XmissionStructures_Results].[Vendor_InspVisit_XmissionStructures_ResultTypeFk], -1) = Coalesce(@Vendor_InspVisit_XmissionStructures_ResultTypeFk, [v_tb_Vendor_InspVisit_XmissionStructures_Results].[Vendor_InspVisit_XmissionStructures_ResultTypeFk], -1)
	AND	IsNull([v_tb_Vendor_InspVisit_XmissionStructures_Results].[LastInspection], '') = Coalesce(@LastInspection, [v_tb_Vendor_InspVisit_XmissionStructures_Results].[LastInspection], '')
	AND	IsNull(Convert(varchar(2000), [v_tb_Vendor_InspVisit_XmissionStructures_Results].[Comments]), '') = Coalesce(Convert(varchar(2000), @Comments), Convert(varchar(2000), [v_tb_Vendor_InspVisit_XmissionStructures_Results].[Comments]), '')
	AND	IsNull([v_tb_Vendor_InspVisit_XmissionStructures_Results].[BElectrical], 0) = Coalesce(@BElectrical, [v_tb_Vendor_InspVisit_XmissionStructures_Results].[BElectrical], 0)

-- primary select
select * from @table


	-- VendorInspVisitXmissionStructuresImageCollection select

select
	v_tb_Vendor_InspVisit_XmissionStructures_Images.* 
from 
	v_tb_Vendor_InspVisit_XmissionStructures_Images 
	JOIN @table x ON v_tb_Vendor_InspVisit_XmissionStructures_Images.Vendor_InspVisit_XmissionStructures_ResultFk = x.Vendor_InspVisit_XmissionStructures_ResultId
	
	
	
GO 





--use PolePosition

if exists (select * from dbo.sysobjects where id = object_id(N'up_VendorInspVisitXmissionStructuresResult_Insert') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure up_VendorInspVisitXmissionStructuresResult_Insert
GO

create proc up_VendorInspVisitXmissionStructuresResult_Insert
	 @Vendor_InspVisit_XmissionStructures_ResultId int =null OUTPUT  
	,@Vendor_InspVisit_XMissionStructure_PoleFk int  
	,@Vendor_InspVisit_XmissionStructures_Inspection_TypeFk int  
	,@Vendor_InspVisit_XmissionStructures_ResultTypeFk int  
	,@LastInspection nvarchar(50) = null  
	,@Comments ntext = null  
	,@bElectrical bit = null  
as
	/* 	Initial Generated Date:  Saturday, February 07, 2009 
		Purpose: Inserts a new record; missing arguments are set as their default (typically null)
	*/

SET NOCOUNT ON	
--int

insert tb_Vendor_InspVisit_XmissionStructures_Results 
	(
	
	 [Vendor_InspVisit_XMissionStructure_PoleFk] 
	,[Vendor_InspVisit_XmissionStructures_Inspection_TypeFk] 
	,[Vendor_InspVisit_XmissionStructures_ResultTypeFk] 
	,[LastInspection] 
	,[Comments] 
	,[bElectrical] 
	)
	values
	(
	
	 @Vendor_InspVisit_XMissionStructure_PoleFk 
	,@Vendor_InspVisit_XmissionStructures_Inspection_TypeFk 
	,@Vendor_InspVisit_XmissionStructures_ResultTypeFk 
	,@LastInspection 
	,@Comments 
	,@bElectrical 
	)
set @Vendor_InspVisit_XmissionStructures_ResultId = scope_identity()
select * from v_tb_Vendor_InspVisit_XmissionStructures_Results where Vendor_InspVisit_XmissionStructures_ResultId = @Vendor_InspVisit_XmissionStructures_ResultId

GO




--use PolePosition

if exists (select * from dbo.sysobjects where id = object_id(N'up_VendorInspVisitXmissionStructuresResult_Update') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure up_VendorInspVisitXmissionStructuresResult_Update
GO


create proc up_VendorInspVisitXmissionStructuresResult_Update
	 @Vendor_InspVisit_XmissionStructures_ResultId int  
	,@Vendor_InspVisit_XMissionStructure_PoleFk int = null  
	,@Vendor_InspVisit_XmissionStructures_Inspection_TypeFk int = null  
	,@Vendor_InspVisit_XmissionStructures_ResultTypeFk int = null  
	,@LastInspection nvarchar(50) = null  
	,@Comments ntext = null  
	,@bElectrical bit = null  
	,@OriginalVendor_InspVisit_XmissionStructures_ResultId int  = null --Original PrimaryKey
	
as
	/* 	Initial Generated Date:  Saturday, February 07, 2009 
		Purpose: Updates an existing record, verifying exist; missing arguments persist original record value
		NOTE: this version of the update allows up to write nulls to the table.
	*/

SET NOCOUNT ON	

if @OriginalVendor_InspVisit_XmissionStructures_ResultId is null
	set @OriginalVendor_InspVisit_XmissionStructures_ResultId = @Vendor_InspVisit_XmissionStructures_ResultId

-- test
declare @Result bit
execute @Result = up_VendorInspVisitXmissionStructuresResult_Test @OriginalVendor_InspVisit_XmissionStructures_ResultId
if (not @Result = 1)
begin
	Declare @x varchar(100)
	Set @x = Convert(Varchar(100), @Vendor_InspVisit_XmissionStructures_ResultId)
	raiserror(505060, 16, 1, 'VendorInspVisitXmissionStructuresResult', @x)
	return
end

-- proceed
update tb_Vendor_InspVisit_XmissionStructures_Results 
set

	 [Vendor_InspVisit_XMissionStructure_PoleFk] = IsNull(@Vendor_InspVisit_XMissionStructure_PoleFk, [source].[Vendor_InspVisit_XMissionStructure_PoleFk])
	,[Vendor_InspVisit_XmissionStructures_Inspection_TypeFk] = IsNull(@Vendor_InspVisit_XmissionStructures_Inspection_TypeFk, [source].[Vendor_InspVisit_XmissionStructures_Inspection_TypeFk])
	,[Vendor_InspVisit_XmissionStructures_ResultTypeFk] = IsNull(@Vendor_InspVisit_XmissionStructures_ResultTypeFk, [source].[Vendor_InspVisit_XmissionStructures_ResultTypeFk])
	,[LastInspection] =  @LastInspection
	,[Comments] =  @Comments
	,[bElectrical] =  @bElectrical
	
from tb_Vendor_InspVisit_XmissionStructures_Results source
where
	[source].[Vendor_InspVisit_XmissionStructures_ResultId] = @OriginalVendor_InspVisit_XmissionStructures_ResultId


select * from v_tb_Vendor_InspVisit_XmissionStructures_Results where Vendor_InspVisit_XmissionStructures_ResultId = @Vendor_InspVisit_XmissionStructures_ResultId
GO





--use PolePosition

if exists (select * from dbo.sysobjects where id = object_id(N'up_VendorInspVisitXmissionStructuresResult_Delete') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure up_VendorInspVisitXmissionStructuresResult_Delete
GO


create proc up_VendorInspVisitXmissionStructuresResult_Delete
	@Vendor_InspVisit_XmissionStructures_ResultId int
as
	/* 	Initial Generated Date:  Saturday, February 07, 2009 
		Purpose: Deletes an existing record, verifying exist
	*/

SET NOCOUNT ON	

-- test
declare @Result bit
execute @Result = up_VendorInspVisitXmissionStructuresResult_Test @Vendor_InspVisit_XmissionStructures_ResultId
if (not @Result = 1)
begin
	Declare @x varchar(100)
	Set @x = Convert(Varchar(100), @Vendor_InspVisit_XmissionStructures_ResultId)
	raiserror(505060, 16, 1, 'VendorInspVisitXmissionStructuresResult', @x)
	return
end

-- proceed
delete from tb_Vendor_InspVisit_XmissionStructures_Results 
where [Vendor_InspVisit_XmissionStructures_ResultId] = @Vendor_InspVisit_XmissionStructures_ResultId

GO


